{
 "cells": [
  {
   "cell_type": "code",
   "id": "initial_id",
   "metadata": {
    "collapsed": true
   },
   "source": [
    "# 1、导入库\n",
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns\n",
    "import datetime\n",
    "from matplotlib import rcParams\n",
    "\n",
    "current_time = datetime.datetime.now()\n",
    "\n",
    "rcParams['font.sans-serif'] = ['SimHei']"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 2、导入数据\n",
    "df = pd.read_csv('data/house_sales.csv')"
   ],
   "id": "9aedaae41d9f10f0",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 3、数据概览\n",
    "print(df.head())"
   ],
   "id": "1a250cf5926f3756",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 4、数据清洗\n",
    "df.drop(columns=['origin_url'], inplace=True)\n",
    "print(df.head())\n",
    "\n",
    "df.isna().sum()\n",
    "df.dropna(inplace=True)\n",
    "\n",
    "df.duplicated().sum()\n",
    "df.drop_duplicates(inplace=True)\n",
    "\n",
    "# 数据类型转换\n",
    "df['area'] = df['area'].str.replace('㎡', '').astype('float')\n",
    "df['price'] = df['price'].str.replace('万', '').astype('float')\n",
    "df['toward'] = df['toward'].astype('category')\n",
    "df['unit'] = df['unit'].str.replace('元/㎡', '').astype('float')\n",
    "df['year'] = df['year'].str.replace('年建', '').astype('int')\n",
    "df.info()\n",
    "\n",
    "# 异常值\n",
    "df = df[(df['area'] > 20) & (df['area'] < 600)]\n",
    "\n",
    "# 房屋售价的异常处理  IQR\n",
    "Q1 = df['price'].quantile(0.25)\n",
    "Q3 = df['price'].quantile(0.75)\n",
    "IQR = Q3 - Q1\n",
    "low_price = Q1 - 1.5 * IQR\n",
    "high_price = Q3 + 1.5 * IQR\n",
    "df = df[(df['price'] < high_price) & (df['price'] > low_price)]\n",
    "\n",
    "df.head(5)\n"
   ],
   "id": "d7dac20b4872a42b",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 5、数据特征构造\n",
    "# 地区 district\n",
    "# 楼层 floor_type\n",
    "# 是否直辖 zxs\n",
    "# 卧室数量 bedrooms\n",
    "# 客厅数量 living_rooms\n",
    "# 楼龄 building_age\n",
    "# 价格分段 price_label\n",
    "\n",
    "df['district'] = df['address'].str.split('-', expand=True)[0]\n",
    "df['floor_type'] = df['floor'].str[:2]\n",
    "df['zxs'] = df['city'].apply(lambda x: 1 if x in ['上海', '北京', '天津', '重庆'] else 0)\n",
    "df['bedrooms'] = df['rooms'].str[:1]\n",
    "df['living_rooms'] = df['rooms'].str[2:].str[:1]\n",
    "df['building_age'] = current_time.year - df['year']\n",
    "df['price_label'] = pd.cut(df['price'], bins=4, labels=['低价', '中价', '高价', '豪华'])\n",
    "\n",
    "df.head(5)"
   ],
   "id": "1cc65fd50ad9980f",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "# 6、数据分析\n",
    "\"\"\"\n",
    "问题编号: A1\n",
    "问题: 哪些变量最影响房价?面积、楼层、房间数哪个影响更大?\n",
    "分析主题:特征相关性\n",
    "分析目标:了解房屋各特征对房价的线性影响\n",
    "分组字段:无\n",
    "指标/方法:皮尔逊相关系数\n",
    "\"\"\"\n",
    "\n",
    "df[['price', 'area', 'unit', 'building_age']].corr()"
   ],
   "id": "bb8725119cce16ea",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "\"\"\"\n",
    "问题编号: A2\n",
    "问题:全国房价总体分布是怎样的?是否存在极端值?分析主题:描述性统计\n",
    "分析目标:概览数值型字段的分布特征分组字段:无\n",
    "指标/方法:平均数/中位数/四分位数/标准差\n",
    "\"\"\"\n",
    "\n",
    "# df.describe()\n",
    "plt.subplot(1, 1, 1)\n",
    "plt.hist(df['price'], bins=4)\n"
   ],
   "id": "d744e87ad2888412",
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-08-04T09:32:42.957713Z",
     "start_time": "2025-08-04T09:32:42.946742Z"
    }
   },
   "cell_type": "code",
   "source": [
    "\"\"\"\n",
    "问题编号:A6\n",
    "问题:南北向是否真比单一朝向贵?贵多少?\n",
    "分析主题:朝向溢价\n",
    "分析目标:评估不同朝向的价格差异\n",
    "分组字段:toward\n",
    "指标/方法:方差分析/多重比较\n",
    "\"\"\"\n",
    "# df.info()\n",
    "df['toward'].value_counts()\n",
    "# df.head()"
   ],
   "id": "c036c25e47c759b1",
   "outputs": [
    {
     "data": {
      "text/plain": [
       "toward\n",
       "南北向    14884\n",
       "南向      8796\n",
       "东南向      974\n",
       "东向       419\n",
       "北向       258\n",
       "西南向      254\n",
       "西向       161\n",
       "东西向      151\n",
       "西北向      133\n",
       "东北向      105\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "execution_count": 22
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
